#!/usr/bin/env bash
dt="`date -d "$d  0 days ago "  "+%Y-%m-%d"`"
mysql -uroot -proot -hhadoop2<<EOF
use oracleanditem;
select substr(Y.time,1,10) date,
Z.OLDUNIQUEID stationid,
count(Y.evaluation_id) dealnumber,
IFNULL(SUM(Y.STAR),0) star1total,
IFNULL(SUM(Y.STAR2),0) star2total,
IFNULL(SUM(Y.STAR3),0) star3total,
IFNULL(SUM(Y.STAR4),0) star4total,
IFNULL(SUM(Y.STAR5),0) star5total
FROM oracleanditem.TBL_EVALUATION AS Y join oracleanditem.TBL_ORGANIZATION AS Z ON Y.ORG_ID=Z.ID
WHERE substr(Y.time,1,10)='$dt'
GROUP BY substr(Y.time,1,10),Z.OLDUNIQUEID ;
EOF